package com.egao.common.core.web;

import com.egao.common.core.Constants;
import com.egao.common.core.config.DateConverterConfig;
import com.egao.common.core.utils.DateUtil;
import net.sf.json.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * @author : Doman
 * @date : 2019-08-30 16:27:48
 * @version ：1.0
 */

public class BaseDao
{
	@Autowired
	public NamedParameterJdbcTemplate jdbcTemplate;
	Logger log = LoggerFactory.getLogger(this.getClass());
	/**
     * 构造查询总数的SQL
     *
     * @param strSQL 需要处理的SQL语句
     * @return 返回经过处理的查询总数的SQL
     */
    public String getCountSQL(String strSQL, Map<String, String> map) {

        // 格式化SQL语句
        String strSQL2 = strSQL.replaceAll("\\s+", " ").trim();
        String regx = "select count\\s*\\(\\s*(\\*|\\w+|\\w+\\.\\*|\\w+\\.\\w+)\\s*\\)\\s+from\\s+";
        Pattern pt = Pattern.compile(regx, Pattern.CASE_INSENSITIVE);
        Matcher mc = pt.matcher(strSQL2);
        // 判断是否已经是查询总数的SQL
        if (mc.find() && mc.start() == 0)
        {
        	getMapParameter(new StringBuffer(strSQL), map);
            return strSQL;
        }
        String reg2 = " order by ";
        Pattern ptOrderNo = Pattern.compile(reg2, Pattern.CASE_INSENSITIVE);
        Matcher mOrderNo = ptOrderNo.matcher(strSQL2);
        int indexOrderNo = -1;//, idx = -1, endCondition = -1;
        while (mOrderNo.find())
        {
		// 查找最后一个order by 的位置
		indexOrderNo = mOrderNo.start();
	}

        if (indexOrderNo>0)
        {
            // 删除order by后面的SQL部分
            strSQL2 = strSQL2.substring(0, indexOrderNo);
            strSQL = strSQL2;
        }
        StringBuffer sqlbuf = new StringBuffer(strSQL.length() + 30);
        sqlbuf.append("select count(1) from (");
        sqlbuf.append(strSQL);
        sqlbuf.append(") aaaa");
        strSQL = sqlbuf.toString();
        getMapParameter(sqlbuf, map);
        return strSQL;
    }

	// 封装分页
	public String getPageSql(String sql, PageResult2<T> page, Map<String, String> map) throws Exception
	{
		if(page.getPage() ==0 || page.getLimit() == 0){throw new Exception("没有获得参数.");}
		String pagedSql = sql + " limit " + (page.getPage()-1)*page.getLimit() + ", " + page.getLimit();
		getMapParameter(new StringBuffer(pagedSql), map);
		return pagedSql;
	}

	public PageResult2<T> getPageQueryListNotOrderBy(PageResult2<T> page, String sql, Map<String, String> map, RowMapper rowMapper) throws Exception
	{
		String sqlCount = "select count(1) from ("+sql+") ccddd";
		getMapParameter(new StringBuffer(sqlCount), map);
		//获取数据总数
		page.setCount(jdbcTemplate.queryForObject(sqlCount,map,new SingleColumnRowMapper<Integer>(Integer.class)));

		//获取查询列表
		List returnList = jdbcTemplate.query(this.getPageSql(sql,page,map),map,rowMapper);
		page.setData(returnList);
		return page;
	}

	public PageResult2<T> getPageQueryList(PageResult2<T> page, String sql, Map<String, String> map, RowMapper rowMapper) throws Exception
	{
		//获取数据总数
		page.setCount(jdbcTemplate.queryForObject(this.getCountSQL(sql,map),map,new SingleColumnRowMapper<Integer>(Integer.class)));

		//获取查询列表
		List returnList = jdbcTemplate.query(this.getPageSql(sql,page,map),map,rowMapper);
		page.setData(returnList);
		return page;
	}

	public void getMapParameter(StringBuffer sql, Map<String, String> args){
		StringBuffer s = new StringBuffer();
		if(null!=args){
			for (Map.Entry<String, String> entry : args.entrySet()) {
				if(s.length()>0){s.append(",");}
				s.append(entry.getKey() + ":" + entry.getValue());
			}
		}
		log.info("时间:{}\n SQL:{}\n 参数:{}", DateUtil.getCurrentDate(), sql.toString(), s);
//		System.out.println(DateUtil.getCurrentDate());
//		System.out.println(sql.toString());
//		System.out.println(s);
	}

	public void getArrayParameter(StringBuffer sql, Object[] arg) {
		StringBuffer s = new StringBuffer();
		for (int i = 0; i < arg.length; i++) {
			s.append(arg[i]+"; ");
		}
		log.info("时间:{}\n SQL:{}\n 参数:{}", DateUtil.getCurrentDate(), sql.toString(), s);
//		System.out.println(DateUtil.formatDate(new Date()));
//		System.out.println(sql.toString());
//		System.out.println(s.toString());
	}
	/**
	 * <p>
	 * 转换为 layui-table 需要的分页格式 JSON
	 * </p>
	 *
	 * @param page 分页对象
	 * @return
	 */
	public String jsonPage(PageResult2<T> page) {
		JSONObject data = new JSONObject();
		data.put("count", page.getCount());
		data.put("data", page.getData());
		data.put("msg", "查询成功");
		data.put("code", 0);
//		System.out.println(data.toString());
		return data.toString();
	}
	/**
	 * <p>
	 * 转换为 layui-table 需要的分页格式 JSON
	 * </p>
	 *
	 * @param page 分页对象
	 * @return
	 */
	public JSONObject jsonPage2(PageResult2<T> page) {
		JSONObject data = new JSONObject();
		data.put("count", page.getCount());
		data.put("data", page.getData());
		data.put("msg", "查询成功");
		data.put("code", 0);
		return data;
	}

	public JsonResult jsonResult(PageResult2<T> page) {
		JsonResult data = new JsonResult();
		data.put("count", page.getCount());
		data.put("data", page.getData());
		data.put("msg", JsonResult.DEFAULT_OK_MSG);
		data.put("code", Constants.RESULT_OK_CODE);
		return data;
	}

}